/*DONE* Table [dbo].[<CompanyName>$Purch_ Inv_ Header$437dbf0e-84ff-417a-965d-ed2bb9650972]  1/2  Script Date: 14.1.2021 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$Purch_ Inv_ Header$437dbf0e-84ff-417a-965d-ed2bb9650972]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$Purch_ Inv_ Header$437dbf0e-84ff-417a-965d-ed2bb9650972](
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Buy-from Vendor No_] [nvarchar](20) NOT NULL,
	[Pay-to Vendor No_] [nvarchar](20) NOT NULL,
	[Pay-to Name] [nvarchar](100) NOT NULL,
	[Pay-to Name 2] [nvarchar](50) NOT NULL,
	[Pay-to Address] [nvarchar](100) NOT NULL,
	[Pay-to Address 2] [nvarchar](50) NOT NULL,
	[Pay-to City] [nvarchar](30) NOT NULL,
	[Pay-to Contact] [nvarchar](100) NOT NULL,
	[Your Reference] [nvarchar](35) NOT NULL,
	[Ship-to Code] [nvarchar](10) NOT NULL,
	[Ship-to Name] [nvarchar](100) NOT NULL,
	[Ship-to Name 2] [nvarchar](50) NOT NULL,
	[Ship-to Address] [nvarchar](100) NOT NULL,
	[Ship-to Address 2] [nvarchar](50) NOT NULL,
	[Ship-to City] [nvarchar](30) NOT NULL,
	[Ship-to Contact] [nvarchar](100) NOT NULL,
	[Order Date] [datetime] NOT NULL,
	[Posting Date] [datetime] NOT NULL,
	[Expected Receipt Date] [datetime] NOT NULL,
	[Posting Description] [nvarchar](100) NOT NULL,
	[Payment Terms Code] [nvarchar](10) NOT NULL,
	[Due Date] [datetime] NOT NULL,
	[Payment Discount _] [decimal](38, 20) NOT NULL,
	[Pmt_ Discount Date] [datetime] NOT NULL,
	[Shipment Method Code] [nvarchar](10) NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Shortcut Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Shortcut Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Vendor Posting Group] [nvarchar](20) NOT NULL,
	[Currency Code] [nvarchar](10) NOT NULL,
	[Currency Factor] [decimal](38, 20) NOT NULL,
	[Prices Including VAT] [tinyint] NOT NULL,
	[Invoice Disc_ Code] [nvarchar](20) NOT NULL,
	[Language Code] [nvarchar](10) NOT NULL,
	[Purchaser Code] [nvarchar](20) NOT NULL,
	[Order No_] [nvarchar](20) NOT NULL,
	[No_ Printed] [int] NOT NULL,
	[On Hold] [nvarchar](3) NOT NULL,
	[Applies-to Doc_ Type] [int] NOT NULL,
	[Applies-to Doc_ No_] [nvarchar](20) NOT NULL,
	[Bal_ Account No_] [nvarchar](20) NOT NULL,
	[Vendor Order No_] [nvarchar](35) NOT NULL,
	[Vendor Invoice No_] [nvarchar](35) NOT NULL,
	[VAT Registration No_] [nvarchar](20) NOT NULL,
	[Sell-to Customer No_] [nvarchar](20) NOT NULL,
	[Reason Code] [nvarchar](10) NOT NULL,
	[Gen_ Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[Transaction Type] [nvarchar](10) NOT NULL,
	[Transport Method] [nvarchar](10) NOT NULL,
	[VAT Country_Region Code] [nvarchar](10) NOT NULL,
	[Buy-from Vendor Name] [nvarchar](100) NOT NULL,
	[Buy-from Vendor Name 2] [nvarchar](50) NOT NULL,
	[Buy-from Address] [nvarchar](100) NOT NULL,
	[Buy-from Address 2] [nvarchar](50) NOT NULL,
	[Buy-from City] [nvarchar](30) NOT NULL,
	[Buy-from Contact] [nvarchar](100) NOT NULL,
	[Pay-to Post Code] [nvarchar](20) NOT NULL,
	[Pay-to County] [nvarchar](30) NOT NULL,
	[Pay-to Country_Region Code] [nvarchar](10) NOT NULL,
	[Buy-from Post Code] [nvarchar](20) NOT NULL,
	[Buy-from County] [nvarchar](30) NOT NULL,
	[Buy-from Country_Region Code] [nvarchar](10) NOT NULL,
	[Ship-to Post Code] [nvarchar](20) NOT NULL,
	[Ship-to County] [nvarchar](30) NOT NULL,
	[Ship-to Country_Region Code] [nvarchar](10) NOT NULL,
	[Bal_ Account Type] [int] NOT NULL,
	[Order Address Code] [nvarchar](10) NOT NULL,
	[Entry Point] [nvarchar](10) NOT NULL,
	[Correction] [tinyint] NOT NULL,
	[Document Date] [datetime] NOT NULL,
	[Area] [nvarchar](10) NOT NULL,
	[Transaction Specification] [nvarchar](10) NOT NULL,
	[Payment Method Code] [nvarchar](10) NOT NULL,
	[Pre-Assigned No_ Series] [nvarchar](20) NOT NULL,
	[No_ Series] [nvarchar](20) NOT NULL,
	[Order No_ Series] [nvarchar](20) NOT NULL,
	[Pre-Assigned No_] [nvarchar](20) NOT NULL,
	[User ID] [nvarchar](50) NOT NULL,
	[Source Code] [nvarchar](10) NOT NULL,
	[Tax Area Code] [nvarchar](20) NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[VAT Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[VAT Base Discount _] [decimal](38, 20) NOT NULL,
	[Prepayment No_ Series] [nvarchar](20) NOT NULL,
	[Prepayment Invoice] [tinyint] NOT NULL,
	[Prepayment Order No_] [nvarchar](20) NOT NULL,
	[Quote No_] [nvarchar](20) NOT NULL,
	[Creditor No_] [nvarchar](20) NOT NULL,
	[Payment Reference] [nvarchar](50) NOT NULL,
	[Dimension Set ID] [int] NOT NULL,
	[Vendor Ledger Entry No_] [int] NOT NULL,
	[Campaign No_] [nvarchar](20) NOT NULL,
	[Buy-from Contact No_] [nvarchar](20) NOT NULL,
	[Pay-to Contact No_] [nvarchar](20) NOT NULL,
	[Responsibility Center] [nvarchar](10) NOT NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[Draft Invoice SystemId] [uniqueidentifier] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Price Calculation Method] [int] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT [' + @Company + '$Purch_ Inv_ Header$437dbf0e-84ff-417a-965d-ed2bb9650972$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$Purch_ Inv_ Header$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END